﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Data.OracleClient;
using System.Configuration;
using UFC.Common;
using UFC.DBUtility;
using System.Xml.Linq;
namespace MESGateWay.WinSMESContainer
{
    public class SendBomDiffToERP
    {
        public void run()
        {
            //获取待推送erp的订单
            string sql = "SELECT t.mfgordername,t.containerid,t.id,t.diff_type FROM mes_send_erp_diff t WHERE t.issend = 0";
            DataTable dtToSend = OracleHelper.Query(sql).Tables[0];

            foreach (DataRow dr in dtToSend.Rows)
            {
                sql = string.Format("UPDATE mes_send_erp_diff t SET t.issend = 1,t.senddate = sysdate WHERE t.id = '{0}'", dr["id"].ToString());
                OracleHelper.ExecuteSql(sql);//第1步：更新订单待推送标记，确保下次循环不再处理。

                DataTable dtBomDiff = new DataTable();

                if (dr["diff_type"].ToString() == "ERP")
                {
                    dtBomDiff = GetMESAndErpBomDiff(dr["mfgordername"].ToString());//获取当前工艺集件和ERP差异信息（ERP）
                }
                else if (dr["diff_type"].ToString() == "MES")
                {
                    dtBomDiff = GetMESHistoryBomDiff(dr);//获取当前工艺集件和历史集件差异信息（MES）
                    if (dtBomDiff.Rows.Count > 0)
                    {
                        deleteHistoryDiff(dr["mfgordername"].ToString());//删除MES历史BOM差异数据
                    }
                }

                foreach (DataRow row in dtBomDiff.Rows)
                {
                    if (row["diffqty"].ToString() == "0")
                    {
                        continue;
                    }
                    Dictionary<string, string> dict = new Dictionary<string, string>();
                    dict["mfgordername"] = row["mfgordername"].ToString();//订单号
                    dict["productname"] = row["productname"].ToString();//子零件号
                    dict["productbatchno"] = row["productbatchno"].ToString();//零件批次号
                    dict["specno"] = row["specno"].ToString();//工序号
                    dict["specname"] = row["opdesc"].ToString();//工序名称
                    dict["diffqty"] = row["diffqty"].ToString();//差异数
                    dict["oper_type"] = row["oper_type"].ToString();//1-数量增加，2-数量减少，3-增加条目，4-删除条目
                    dict["bomid"] = row["bomid"].ToString();//bomid
                    dict["diff_type"] = dr["diff_type"].ToString();//差异类型
                    SaveBomDiffData(dict);//第2步：保存bom差异信息到数据库
                }

                string xml = CreateXML(dtBomDiff);
                //第3步：调用接口，给ERP发送差异信息
                //本地开发环境调用
                ERPWebservice.TCMTOMESservice erp = new ERPWebservice.TCMTOMESservice();
                string msg = erp.ERP_Receive_MES_BOM_Diff(xml);

                //正式环境调用
                //ERPWebservice.ZMM_S_001 zmm = new ERPWebservice.ZMM_S_001();
                //zmm.I_DATA = xml;

                //ERPWebservice.ZFM_MES_TO_SAP_001 zfm = new ERPWebservice.ZFM_MES_TO_SAP_0011();
                //zfm.I_INPUT = zmm;

                //ERPWebservice.ZFM_MES_TO_SAP_001 erp = new ERPWebservice.ZFM_MES_TO_SAP_001();
                //var response = erp.CALLZFM_MES_TO_SAP_001(zfm);
                //string msg = response.E_OUTPUT.I_DATA;

                Dictionary<string, object> dictErpResult = GetErpReturnMsg(msg);

                UpdateBomDiffInfo(dtBomDiff, dictErpResult);//第3步：更新bom差异表状态信息

                if (Convert.ToBoolean(dictErpResult["isSuccess"]))
                {
                    //第4步：ERP返回消息成功，将ERP推送的组件信息（BOM）删除,然后重新全量插入表
                    DeleteErpSendMaterial(dr["mfgordername"].ToString());
                    InsertBomMaterial(msg);
                }
            }
        }

        Dictionary<string, object> GetErpReturnMsg(string xml)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(xml);
                string RTYPE = xmlDoc.SelectSingleNode("//RESPMSG//RTYPE").InnerText;
                string RTMSG = xmlDoc.SelectSingleNode("//RESPMSG//RTMSG").InnerText;
                Dictionary<string, object> dict = new Dictionary<string, object>();
                dict["isSuccess"] = RTYPE == "S" ? true : false;
                dict["msg"] = RTMSG;
                return dict;
            }
            catch (Exception ex)
            {
                Dictionary<string, object> dict = new Dictionary<string, object>();
                dict["isSuccess"] = false;
                dict["msg"] = ex.ToString();
                return dict;
            }
        }

        //查询MES和ERP差异
        DataTable GetMESAndErpBomDiff(string mfgordername)
        {
            string sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,mfg.plannedstartdate,pb3.productname mfgproductname,
       mfg.plannedcompletiondate,sb2.specname,s.specno,s.opdesc,s.specid,pbn.productname,
       b.bomid,bb.bomname,pml.qtyrequired,mfg.qty mfgqty,
       (pml.qtyrequired * mfg.qty) mesqty,NVL(erp.qty,0) erpqty,
       (pml.qtyrequired * mfg.qty) - NVL(erp.qty,0) diffqty,
       CASE WHEN erp.id IS NULL THEN '3'
            WHEN erp.id IS NOT NULL AND erp.qty > (pml.qtyrequired * mfg.qty) THEN '2' 
            WHEN erp.id IS NOT NULL AND erp.qty < (pml.qtyrequired * mfg.qty) THEN '1' 
            WHEN erp.id IS NOT NULL AND erp.qty = (pml.qtyrequired * mfg.qty) THEN '0'
       END oper_type,
       erp.dumps,erp1.rsnum,erp.schgt,erp.uom,erp.rspos,erp.productbatchno
FROM mfgorder mfg
--查订单件号信息
LEFT JOIN productbase pb2 ON pb2.productbaseid = mfg.productbaseid
LEFT JOIN product p2 ON p2.productid = NVL(pb2.revofrcdid,mfg.productid)
LEFT JOIN productbase pb3 ON pb3.productbaseid=p2.productbaseid
--查件号默认工艺信息
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = p2.workflowbaseid
LEFT JOIN workflow wf ON wf.workflowid = NVL(wfb.revofrcdid,p2.workflowid)
--bom信息
INNER JOIN bom b ON b.workflowid = wf.workflowid
LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid
LEFT JOIN productmateriallistitem pml ON pml.bomid = b.bomid
--bom工序信息
LEFT JOIN specbase sb ON sb.specbaseid=pml.specbaseid
LEFT JOIN spec s ON  s.specid=NVL(sb.revofrcdid, pml.specid)
LEFT JOIN specbase sb2 ON sb2.specbaseid = s.specbaseid
--子零件信息
LEFT JOIN productbase pb ON pb.productbaseid = pml.productbaseid
LEFT JOIN product p ON p.productid =NVL(pb.revofrcdid, pml.productid)
LEFT JOIN productbase pbn ON pbn.productbaseid=p.productbaseid
LEFT JOIN erp_send_material erp ON erp.mfgordername = mfg.mfgordername AND erp.productname = pbn.productname AND erp.specno = s.specno AND erp.bomid = b.bomid --erp发料信息
LEFT JOIN (
     SELECT erp.mfgordername,erp.rsnum
     FROM erp_send_material erp
     WHERE erp.mfgordername = '{0}'
     GROUP BY erp.mfgordername,erp.rsnum
) erp1 ON erp1.mfgordername = mfg.mfgordername
WHERE mfg.mfgordername = '{0}'
ORDER BY s.specno";
            sql = string.Format(sql, mfgordername);

            DataTable dtResult = OracleHelper.Query(sql).Tables[0];

            //查询MES的BOM里没有，但是ERP的BOM里有的子零件信息
            string sql2 = @"SELECT * FROM erp_send_material erp WHERE erp.bomid IS NULL AND erp.mfgordername = '{0}'";
            sql2 = string.Format(sql2, mfgordername);
            DataTable dt2 = OracleHelper.Query(sql2).Tables[0];

            if (dt2.Rows.Count > 0)
            {
                foreach (DataRow dr in dt2.Rows)
                {
                    DataRow newrow = dtResult.NewRow();
                    newrow["mfgordername"] = dr["mfgordername"].ToString();
                    newrow["productname"] = dr["productname"].ToString();
                    newrow["productbatchno"] = dr["productbatchno"].ToString();
                    newrow["specno"] = dr["specno"].ToString();
                    newrow["specname"] = "";
                    newrow["diffqty"] = "-" + dr["qty"].ToString();//差异数为负值
                    newrow["mesqty"] = "0";//需要删除条目的零件，MES需求数量为0
                    newrow["oper_type"] = "4";//4:删除条目
                    newrow["bomid"] = "";
                    newrow["dumps"] = dr["dumps"].ToString();//虚拟项目标识
                    newrow["schgt"] = dr["schgt"].ToString();//散装物料标识
                    newrow["rspos"] = dr["rspos"].ToString();//项目编号
                    newrow["rsnum"] = dr["rsnum"].ToString();//业务号
                    dtResult.Rows.Add(newrow);
                }
            }
            return dtResult;
        }

        //生成xml
        string CreateXML(DataTable dt)
        {
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.AppendChild(doc.CreateXmlDeclaration("1.0", "utf-8", ""));
                XmlNode documentNode = doc.CreateElement("document");

                //订单号
                XmlNode AUFNR = doc.CreateElement("AUFNR");
                AUFNR.InnerText = dt.Rows[0]["mfgordername"].ToString();
                documentNode.AppendChild(AUFNR);

                //预留业务号
                XmlNode RSNUM = doc.CreateElement("RSNUM");
                RSNUM.InnerText = dt.Rows[0]["rsnum"].ToString();
                documentNode.AppendChild(RSNUM);

                XmlNode ItemsNode = doc.CreateElement("Items");
                foreach (DataRow dr in dt.Rows)
                {
                    XmlNode ItemNode = doc.CreateElement("Item");

                    //物料编码
                    XmlNode MATNR = doc.CreateElement("MATNR");
                    MATNR.InnerText = dr["productname"].ToString();
                    ItemNode.AppendChild(MATNR);

                    //差异数量
                    XmlNode BDMNG = doc.CreateElement("BDMNG");
                    BDMNG.InnerText = dr["mesqty"].ToString();//按照erp项目组要求，差异数量为MES需求数量，若mes中没有该零件，传递给erp的差异数量为0
                    ItemNode.AppendChild(BDMNG);

                    //计量单位
                    XmlNode MEINS = doc.CreateElement("MEINS");
                    MEINS.InnerText = dr["uom"].ToString();
                    ItemNode.AppendChild(MEINS);

                    //工序号
                    XmlNode VORNR = doc.CreateElement("VORNR");
                    VORNR.InnerText = dr["specno"].ToString();
                    ItemNode.AppendChild(VORNR);

                    //预定的项目编号
                    XmlNode RSPOS = doc.CreateElement("RSPOS");
                    RSPOS.InnerText = dr["rspos"].ToString();
                    ItemNode.AppendChild(RSPOS);

                    //差异类型
                    XmlNode OperateType = doc.CreateElement("OPERATETYPE");
                    OperateType.InnerText = dr["oper_type"].ToString();
                    ItemNode.AppendChild(OperateType);

                    //散装物料标识
                    XmlNode SCHGT = doc.CreateElement("SCHGT");
                    SCHGT.InnerText = dr["schgt"].ToString();
                    ItemNode.AppendChild(SCHGT);

                    //散装物料标识
                    XmlNode DUMPS = doc.CreateElement("DUMPS");
                    DUMPS.InnerText = dr["dumps"].ToString();
                    ItemNode.AppendChild(DUMPS);

                    ItemsNode.AppendChild(ItemNode);
                }
                documentNode.AppendChild(ItemsNode);
                doc.AppendChild(documentNode);
                return doc.InnerXml;
            }
            catch (Exception e)
            {
                LogHelp.WriteError("推送给ERP的差异XML", e);
                return string.Empty;
            }
        }

        //保存差异信息
        void SaveBomDiffData(Dictionary<string, string> dict)
        {
            string sql = @"SELECT * FROM erp_mes_bom_diff t WHERE t.erp_status = 0 AND t.mfgordername = '{0}' AND t.productname = '{1}' AND t.specno = '{2}'";
            sql = string.Format(sql, dict["mfgordername"], dict["productname"], dict["specno"]);
            if (dict["bomid"] == "")
            {
                sql += string.Format(" AND t.bomid IS NULL");
            }
            else
            {
                sql += string.Format(" AND t.bomid = '{0}'", dict["bomid"]);
            }

            DataTable dt = OracleHelper.Query(sql).Tables[0];
            if (dt.Rows.Count > 0) { return; }
            string insertSql = @"
INSERT INTO erp_mes_bom_diff(id,mfgordername,productname,productbatchno,specno,specname,diffqty,oper_type,bomid,diff_type) 
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";

            insertSql = string.Format(insertSql,
                Guid.NewGuid().ToString(),//唯一id
                dict["mfgordername"],//订单号
                dict["productname"],//子零件号
                dict["productbatchno"],//零件批次号
                dict["specno"],//工序号
                dict["specname"],//工序名称
                dict["diffqty"],//差异数
                dict["oper_type"],//1-数量增加，2-数量减少，3-增加条目，4-删除条目
                dict["bomid"],
                dict["diff_type"]//差异类型
                );
            OracleHelper.ExecuteSql(insertSql);
        }

        void UpdateBomDiffInfo(DataTable dtBomDiff, Dictionary<string, object> para)
        {
            foreach (DataRow dr in dtBomDiff.Rows)
            {
                string mfgordername = dr["mfgordername"].ToString();
                string productname = dr["productname"].ToString();
                string specno = dr["specno"].ToString();
                string bomid = dr["bomid"].ToString();

                string sql = @"SELECT t.id FROM erp_mes_bom_diff t WHERE t.erp_status = 0 AND t.mfgordername = '{0}' AND t.productname = '{1}' AND t.specno = '{2}'";
                sql = string.Format(sql, mfgordername, productname, specno);
                if (bomid == "")
                {
                    sql += string.Format(" AND t.bomid IS NULL");
                }
                else
                {
                    sql += string.Format(" AND t.bomid = '{0}'", bomid);
                }

                DataTable dt = OracleHelper.Query(sql).Tables[0];

                if (dt.Rows.Count == 0) { continue; }

                foreach (DataRow row in dt.Rows)
                {
                    string updateSql = @"
UPDATE erp_mes_bom_diff t 
SET t.erp_status = '{0}',t.erp_msg = '{1}',t.erp_return_date = sysdate,t.createdate = sysdate 
WHERE t.id = '{2}'";
                    updateSql = string.Format(updateSql, Convert.ToBoolean(para["isSuccess"]) == true ? 1 : 0, para["msg"].ToString(), row["id"].ToString());
                    OracleHelper.ExecuteSql(updateSql);
                }
            }
        }

        //删除ERP发送的bom信息
        void DeleteErpSendMaterial(string mfgordername)
        {
            string copySql = string.Format("INSERT INTO erp_send_material_backup SELECT * FROM erp_send_material t WHERE t.mfgordername = '{0}'", mfgordername);
            OracleHelper.ExecuteSql(copySql);//删除前先把数据保存到备份表中去
            string deleteSql = string.Format(@"DELETE erp_send_material t WHERE t.mfgordername = '{0}'", mfgordername);
            OracleHelper.ExecuteSql(deleteSql);
        }

        //插入erp零组件信息, msg:ERP返回的完整BOMxml
        void InsertBomMaterial(string msg)
        {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(msg);

            string orderName = xmlDoc.SelectSingleNode("//document/AUFNR").InnerText;//生产订单号
            string RSNUM = xmlDoc.SelectSingleNode("//document/RSNUM").InnerText;//预留业务号

            string sql = string.Format(@"SELECT * FROM mfgorder t WHERE t.mfgordername = '{0}'", orderName);
            DataSet ds = OracleHelper.Query(sql);
            if (ds.Tables[0].Rows.Count == 0)
            {
                LogHelp.WriteLog(string.Format("订单:['{0}']在MES系统中不存在", orderName));
            }

            Dictionary<string, string> para = new Dictionary<string, string>();
            para["mfgordername"] = orderName;
            DataTable dtDiff = GetBomInfo(orderName);

            XmlNodeList itemNodes = xmlDoc.SelectNodes("//document/Items/Item");

            foreach (XmlNode xn in itemNodes)
            {
                if (xn.Name != "Item") { continue; }

                Dictionary<string, string> dict = new Dictionary<string, string>();
                dict.Add("MFGORDERNAME", orderName);//订单号
                dict.Add("RSNUM", RSNUM);

                string productno = xn.SelectSingleNode("MATNR").InnerText;//物料编码
                dict.Add("PRODUCTNAME", productno);

                string qty = xn.SelectSingleNode("BDMNG").InnerText;//发出数量
                dict.Add("QTY", qty);

                string uom = xn.SelectSingleNode("MEINS").InnerText;//计量单位
                dict.Add("UOM", uom);

                var containerflag = xn.SelectSingleNode("CHARG");//零件批次号
                dict.Add("PRODUCTBATCHNO", containerflag == null ? "" : containerflag.InnerText);

                var specno = xn.SelectSingleNode("VORNR");//工序号
                dict.Add("SPECNO", specno == null ? "" : specno.InnerText);

                var RSPOS = xn.SelectSingleNode("RSPOS");//预定的项目编号
                dict.Add("RSPOS", RSPOS == null ? "" : RSPOS.InnerText);

                var SCHGT = xn.SelectSingleNode("SCHGT");//散装物料标识
                dict.Add("SCHGT", SCHGT == null ? "" : SCHGT.InnerText);

                var DUMPS = xn.SelectSingleNode("DUMPS");//虚拟项目标识
                dict.Add("DUMPS", DUMPS == null ? "" : DUMPS.InnerText);

                var LGORT = xn.SelectSingleNode("LGORT");//库存地点
                dict.Add("LGORT", LGORT == null ? "" : LGORT.InnerText);

                var LIFNR = xn.SelectSingleNode("LIFNR");//供应商编码
                dict.Add("LIFNR", LIFNR == null ? "" : LIFNR.InnerText);

                string filter = string.Format("mfgordername = '{0}' AND productname = '{1}' AND specno = '{2}'", orderName, productno, dict["SPECNO"]);
                DataRow[] drList = dtDiff.Select(filter);
                if (drList.Length > 0)
                {
                    dict.Add("SPECNAME", drList[0]["opdesc"].ToString());//工序名称
                    dict.Add("bomid", drList[0]["bomid"].ToString());
                }
                else
                {
                    dict.Add("SPECNAME", "");
                    dict.Add("bomid", "");
                }
                string message = SaveErpSendMaterial(dict);
            }
        }

        //插入BOM推送订单组件信息
        string SaveErpSendMaterial(Dictionary<string, string> para)
        {
            string query = @"SELECT t.id FROM erp_send_material t WHERE t.mfgordername = '{0}' AND t.productname = '{1}' AND t.specno = '{2}' AND t.bomid = '{3}'";
            query = string.Format(query, para["MFGORDERNAME"], para["PRODUCTNAME"], para["SPECNO"], para["bomid"]);
            DataTable dt = OracleHelper.Query(query).Tables[0];

            if (dt.Rows.Count > 0) { return "重复推送订单"; }

            string sql = @"
INSERT INTO erp_send_material(id,mfgordername,productbatchno,productname,qty,uom,specno,rspos,rsnum,specname,schgt,dumps,bomid,lgort,lifnr)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')";
            sql = string.Format(sql,
                Guid.NewGuid().ToString(),//唯一id
                para["MFGORDERNAME"],//订单号
                para["PRODUCTBATCHNO"],//零件批次号
                para["PRODUCTNAME"],//零部件号
                para["QTY"],//发出数量
                para["UOM"],//计量单位
                para["SPECNO"],//工序号
                para["RSPOS"],//项目编号
                para["RSNUM"],//预留
                para["SPECNAME"],//工序名称
                para["SCHGT"],//散装物料标识
                para["DUMPS"],//虚拟项目标识
                para["bomid"],//bomid
                para["LGORT"],//库存地点
                para["LIFNR"]//供应商编码
                );
            int insertCount = OracleHelper.ExecuteSql(sql);
            return insertCount > 0 ? "创建成功" : "创建失败";
        }

        //获取当前工艺集件和历史集件差异信息
        DataTable GetMESHistoryBomDiff(DataRow dr)
        {
            string sql = @"
--以新的为基准
SELECT cci.mfgordername,pbn.productname,erp.productbatchno,s.specno,s.opdesc,
       pml.qtyrequired * c.originalqty - NVL(erp.qty,0) diffqty,
       CASE WHEN erp.id IS NULL THEN '3'
            WHEN erp.id IS NOT NULL AND erp.qty > (pml.qtyrequired * c.originalqty) THEN '2' 
            WHEN erp.id IS NOT NULL AND erp.qty < (pml.qtyrequired * c.originalqty) THEN '1' 
       END oper_type,b.bomid,erp.rsnum,pml.qtyrequired * c.originalqty mesqty,uu.uomname uom,erp.rspos,erp.schgt,erp.dumps 
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN workflow wf ON wf.workflowid = cci.workflowid
--bom信息
INNER JOIN bom b ON b.workflowid = wf.workflowid
LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid
LEFT JOIN productmateriallistitem pml ON pml.bomid = b.bomid
--bom工序信息
LEFT JOIN specbase sb ON sb.specbaseid=pml.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid, pml.specid)
LEFT JOIN specbase sb2 ON sb2.specbaseid = s.specbaseid
--子零件信息
LEFT JOIN productbase pb ON pb.productbaseid = pml.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid, pml.productid)
LEFT JOIN productbase pbn ON pbn.productbaseid=p.productbaseid
LEFT JOIN erp_send_material erp ON erp.mfgordername = cci.mfgordername AND erp.productname = pbn.productname AND erp.specno = s.specno
LEFT JOIN uom uu ON uu.uomid = pml.uomid
WHERE cci.containerid = '{0}' AND NVL(erp.qty,0) <> (pml.qtyrequired * c.originalqty)

UNION ALL

SELECT erp.mfgordername,erp.productname,erp.productbatchno,
       erp.specno,erp.specname opdesc,
       -erp.qty diffqty,
       '4' oper_type,'' bomid,erp.rsnum,0 mesqty,erp.uom,erp.rspos,erp.schgt,erp.dumps
FROM erp_send_material erp
LEFT JOIN (
    SELECT cci.mfgordername,pbn.productname,s.specno
    FROM containercurrentinfo cci
    LEFT JOIN container c ON c.containerid = cci.containerid
    LEFT JOIN workflow wf ON wf.workflowid = cci.workflowid
    --bom信息
    INNER JOIN bom b ON b.workflowid = wf.workflowid
    LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid
    LEFT JOIN productmateriallistitem pml ON pml.bomid = b.bomid
    --bom工序信息
    LEFT JOIN specbase sb ON sb.specbaseid=pml.specbaseid
    LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid, pml.specid)
    LEFT JOIN specbase sb2 ON sb2.specbaseid = s.specbaseid
    --子零件信息
    LEFT JOIN productbase pb ON pb.productbaseid = pml.productbaseid
    LEFT JOIN product p ON p.productid =NVL(pb.revofrcdid, pml.productid)
    LEFT JOIN productbase pbn ON pbn.productbaseid=p.productbaseid
    WHERE cci.containerid = '{0}'
) t ON t.mfgordername = erp.mfgordername AND t.productname = erp.productname AND t.specno = erp.specno
WHERE erp.mfgordername = '{1}' AND t.productname IS NULL";
            sql = string.Format(sql, dr["containerid"].ToString(), dr["mfgordername"].ToString());
            return OracleHelper.Query(sql).Tables[0];
        }

        //查询bom信息
        DataTable GetBomInfo(string mfgordername)
        {
            string sql = @"
SELECT mfg.mfgorderid,mfg.mfgordername,mfg.plannedstartdate,pb3.productname mfgproductname,
       mfg.plannedcompletiondate,sb2.specname,s.specno,s.opdesc,s.specid,pbn.productname,
       b.bomid,bb.bomname,pml.qtyrequired,mfg.qty mfgqty,
       (pml.qtyrequired * mfg.qty) mesqty
FROM mfgorder mfg
--查订单件号信息
LEFT JOIN productbase pb2 ON pb2.productbaseid = mfg.productbaseid
LEFT JOIN product p2 ON p2.productid = NVL(pb2.revofrcdid,mfg.productid)
LEFT JOIN productbase pb3 ON pb3.productbaseid=p2.productbaseid
--查件号默认工艺信息
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = p2.workflowbaseid
LEFT JOIN workflow wf ON wf.workflowid = NVL(wfb.revofrcdid,p2.workflowid)
--bom信息
INNER JOIN bom b ON b.workflowid = wf.workflowid
LEFT JOIN bombase bb ON bb.bombaseid = b.bombaseid
LEFT JOIN productmateriallistitem pml ON pml.bomid = b.bomid
--bom工序信息
LEFT JOIN specbase sb ON sb.specbaseid=pml.specbaseid
LEFT JOIN spec s ON  s.specid=NVL(sb.revofrcdid, pml.specid)
LEFT JOIN specbase sb2 ON sb2.specbaseid = s.specbaseid
--子零件信息
LEFT JOIN productbase pb ON pb.productbaseid = pml.productbaseid
LEFT JOIN product p ON p.productid =NVL(pb.revofrcdid, pml.productid)
LEFT JOIN productbase pbn ON pbn.productbaseid=p.productbaseid
WHERE mfg.mfgordername = '{0}'";
            sql = string.Format(sql, mfgordername);
            return OracleHelper.Query(sql).Tables[0];
        }

        //删除MES历史bom差异数据
        void deleteHistoryDiff(string mfgordername)
        {
            string deleteSql = "DELETE erp_mes_bom_diff dif WHERE dif.diff_type = 'MES' AND dif.mfgordername = '{0}'";
            deleteSql = string.Format(deleteSql, mfgordername);
            OracleHelper.ExecuteSql(deleteSql);
        }
    }
}